<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class M_lib_circulationfile2 extends MY_Model {
	
	protected $_table = "lib_circulationfile2";
	protected $_uid = "id";

	public function __construct(){
		parent::__construct();
	}

	/**
	 * Remove Selected Details
	 *@param ids array
	 */	
	public function remove_details($ids)
	{
		if($ids && is_array($ids)){

			foreach ($ids as $key => $id) {

				$det = $this->get($id);
				if(!$det){ continue; }

				unset($data);
				$data['is_deleted'] = 1;
				$data['deleted_by'] = $this->session->userdata['userid'];
				$data['date_deleted'] = NOW;
				$rs = $this->update($id, $data);
				if($rs){

					//updated book_borrowed
					$this->db->query("UPDATE library_books set book_borrowed = book_borrowed - 1 WHERE book_id = ?", array($det->media_id));

				}
			}

			return true;
		}

		return false;
	}

	/**
	 * Return Selected Details
	 *@param ids array
	 */	
	public function return_details($ids)
	{
		$this->load->model('M_library_books');
		$this->load->model('M_lib_circulationfile1');
		$this->load->helper('library');

		$circulation_id = false;

		if($ids && is_array($ids)){

			$ctr = 0;
			$count = count($ids);

			foreach ($ids as $key => $id) {

				$det = $this->get($id);	
				if(!$det){ continue; }

				$circulation_id = $det->circulation_id;

				/*** Get Media ***/
				$item = $this->M_library_books->get($det->media_id); if(!$item){ continue; }

				unset($data);
				$data['retdte'] = $ret = NOW;
				$data['cir_status'] = 'RETURN';
				
				/*** Check if Book Late ***/
				$exp_retdte = date('Y-m-d',strtotime($det->trndte) + (24*3600*$det->day));
				$ret_dte = date('Y-m-d', strtotime($ret));
				
				if(is_date_late($ret_dte, $exp_retdte)){
					$data['is_late'] = 1;
					$data['day_late'] = abs(day_diff($ret_dte, $exp_retdte));
				}

				$rs = $this->update($id, $data);
				if($rs){

					$ctr++;

					//updated book_borrowed
					$this->db->query("UPDATE library_books set book_borrowed = book_borrowed - 1 WHERE book_id = ?", array($det->media_id));
				}
			}

			if($ctr > 0){

				$this->M_lib_circulationfile1->update_status($circulation_id);
			}

			return true;
		}

		return false;
	}

	/**
	 * Get Borrowers
	 * Get Borrowers of the given media
	 * @media_id int
	 * @return object
	 */	
	public function get_borrowers($media_id)
	{
		$sly = false;

		$sql = "
			SELECT
				lib1.`borrower_id`,
				lib1.`usertype`,
				lib2.`media_id`,
				COUNT(lib2.`media_id`) AS book_count
			FROM `lib_circulationfile2` lib2
			LEFT JOIN `lib_circulationfile1` lib1 ON lib2.`circulation_id` = lib1.`id`
			WHERE lib2.`is_deleted` = 0
			AND lib2.`cir_status` <> 'RETURN'
			AND lib2.media_id = ?
			GROUP BY lib1.`borrower_id`;
		";

		$rs = $this->query($sql,array($media_id));

		if($rs){

			$this->load->model('M_student');
			$this->load->model('M_employees');

			foreach ($rs as $key => $bwr) {
				if($bwr->usertype === "student"){
					$user = $this->M_student->get_student($bwr->borrower_id);
				}else{
					$user = $this->M_employees->get($bwr->borrower_id);
				}

				$data['borrower_id'] = $bwr->borrower_id;
				$data['usertype'] = $bwr->usertype;
				$data['profile'] = $user;
				$data['copy'] = $bwr->book_count;

				$sly[] = (object)$data;
			}

			return $sly;
		}

		return false;
	}

	/**
	 * circulation report by date
	 * @param array from post
	 */
	public function circulation_report_by_date($trndte)
	{
		$this->load->model(array(
				'M_media_types',
				'M_student',
				'M_library_books',
				'M_library_book_category',
				'M_employees'
			));
		$this->load->model('M_lib_circulationfile1','m_lib_c1');

		unset($get);
			$get['where']['DATE(lib_circulationfile2.trndte) >= '] = $trndte['from'];
			$get['where']['DATE(lib_circulationfile2.trndte) <= '] = $trndte['to'];
			$get['where']['lib_circulationfile2.is_deleted'] = 0;
			$get['order'] = 'lib_circulationfile2.trndte, lib_circulationfile1.borrower_id';
			if($trndte['media_type_id']){
				$get['where']['library_books.media_type_id'] = $trndte['media_type_id'];
			}
			if($trndte['cir_status']){
				$get['where']['lib_circulationfile2.cir_status'] = $trndte['cir_status'];
			}
			$get['join'][] = array(
					'table' => 'lib_circulationfile1',
					'on' => 'lib_circulationfile1.id = lib_circulationfile2.circulation_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_books',
					'on' => 'library_books.book_id = lib_circulationfile2.media_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'media_types',
					'on' => 'media_types.id = library_books.media_type_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_book_category',
					'on' => 'library_book_category.lbc_id = library_books.book_category',
					'type' => 'left'
				);
			$get['fields'] = array(
					"library_books.book_id",
					"library_books.book_category",
					"library_books.book_name",
					"library_books.book_desc",
					"library_books.book_copies",
					"library_books.book_isbn",
					"library_books.book_author",
					"library_books.book_publisher",
					"library_books.book_dop",
					"library_books.book_created",
					"library_books.book_updated",
					"library_books.media_type_id",
					"library_books.book_borrowed",
					"library_books.book_barcode",
					"media_types.media_type",
					"library_book_category.lbc_name as category",
					"lib_circulationfile2.circulation_id",
					"lib_circulationfile2.trndte as trndte_raw",
					"lib_circulationfile2.retdte as retdte_raw",
					"DATE_FORMAT(lib_circulationfile2.trndte,'%m-%d-%Y') as trndte",
					"DATE_FORMAT(lib_circulationfile2.retdte,'%m-%d-%Y') as retdte",
					"lib_circulationfile2.day",
					"lib_circulationfile2.cir_status",
					"lib_circulationfile2.is_late",
					"lib_circulationfile2.day_late",
					"lib_circulationfile2.remarks",
					"lib_circulationfile1.usertype",
					"lib_circulationfile1.borrower_id",
					"lib_circulationfile2.id",
				);

			$rs = $this->get_record(false, $get);

			$borrowers = false;
			if($rs){
				foreach ($rs as $k => $v) {
					
					/** GET BORROWERS INFO FROM EACH RECORD DEPENDS ON USERTYPE (STUDENT|EMPLOYEE) **/
					if($v->usertype === "student"){
						$user = $this->M_student->get_student($v->borrower_id);
					}else{
						$user = $this->M_employees->get($v->borrower_id);
					}

					$borrowers[$v->id] = $user;
				}
			}
			// vd($borrowers);
			$sly['transactions'] = $rs;
			$sly['borrowers'] = $borrowers;

			return $sly;
	}

	/**
	 * circulation report by return date
	 * @param array from post
	 */
	public function circulation_report_by_retdte($retdte)
	{
		$this->load->model(array(
				'M_media_types',
				'M_student',
				'M_library_books',
				'M_library_book_category',
				'M_employees'
			));
		$this->load->model('M_lib_circulationfile1','m_lib_c1');

		unset($get);
			$get['where']['lib_circulationfile2.cir_status'] = 'RETURN';
			$get['where']['DATE(lib_circulationfile2.retdte) >= '] = $retdte['from'];
			$get['where']['DATE(lib_circulationfile2.retdte) <= '] = $retdte['to'];
			$get['where']['lib_circulationfile2.is_deleted'] = 0;
			$get['order'] = 'lib_circulationfile2.retdte, lib_circulationfile1.borrower_id';
			if($retdte['media_type_id']){
				$get['where']['library_books.media_type_id'] = $retdte['media_type_id'];
			}
			if($retdte['cir_status']){
				$get['where']['lib_circulationfile2.cir_status'] = $retdte['cir_status'];
			}
			$get['join'][] = array(
					'table' => 'lib_circulationfile1',
					'on' => 'lib_circulationfile1.id = lib_circulationfile2.circulation_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_books',
					'on' => 'library_books.book_id = lib_circulationfile2.media_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'media_types',
					'on' => 'media_types.id = library_books.media_type_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_book_category',
					'on' => 'library_book_category.lbc_id = library_books.book_category',
					'type' => 'left'
				);
			$get['fields'] = array(
					"library_books.book_id",
					"library_books.book_category",
					"library_books.book_name",
					"library_books.book_desc",
					"library_books.book_copies",
					"library_books.book_isbn",
					"library_books.book_author",
					"library_books.book_publisher",
					"library_books.book_dop",
					"library_books.book_created",
					"library_books.book_updated",
					"library_books.media_type_id",
					"library_books.book_borrowed",
					"library_books.book_barcode",
					"media_types.media_type",
					"library_book_category.lbc_name as category",
					"lib_circulationfile2.circulation_id",
					"lib_circulationfile2.trndte as trndte_raw",
					"lib_circulationfile2.retdte as retdte_raw",
					"DATE_FORMAT(lib_circulationfile2.trndte,'%m-%d-%Y') as trndte",
					"DATE_FORMAT(lib_circulationfile2.retdte,'%m-%d-%Y') as retdte",
					"lib_circulationfile2.day",
					"lib_circulationfile2.cir_status",
					"lib_circulationfile2.is_late",
					"lib_circulationfile2.day_late",
					"lib_circulationfile2.remarks",
					"lib_circulationfile1.usertype",
					"lib_circulationfile1.borrower_id",
					"lib_circulationfile2.id",
				);

			$rs = $this->get_record(false, $get);

			$borrowers = false;
			if($rs){
				foreach ($rs as $k => $v) {
					
					/** GET BORROWERS INFO FROM EACH RECORD DEPENDS ON USERTYPE (STUDENT|EMPLOYEE) **/
					if($v->usertype === "student"){
						$user = $this->M_student->get_student($v->borrower_id);
					}else{
						$user = $this->M_employees->get($v->borrower_id);
					}

					$borrowers[$v->id] = $user;
				}
			}
			// vd($rs);
			$sly['transactions'] = $rs;
			$sly['borrowers'] = $borrowers;

			return $sly;
	}

	/**
	 * circulation report by borrower
	 * @param array from post
	 */
	public function circulation_report_by_borrower($borrower)
	{
		
		$this->load->model(array(
				'M_media_types',
				'M_student',
				'M_library_books',
				'M_library_book_category',
				'M_employees'
			));
		$this->load->model('M_lib_circulationfile1','m_lib_c1');

		/** SEPARATE STUDENT AND EMPLOYEE **/
		if($borrower['usertype'] === "student"){

			unset($get);
			$get['where']['DATE(lib_circulationfile2.trndte) >= '] = $borrower['from'];
			$get['where']['DATE(lib_circulationfile2.trndte) <= '] = $borrower['to'];
			$get['where']['lib_circulationfile1.usertype'] = 'student';
			$get['where']['lib_circulationfile2.is_deleted'] = 0;
			$get['order'] = 'borrowers_fullname, lib_circulationfile2.trndte';
			if($borrower['media_type_id']){
				$get['where']['library_books.media_type_id'] = $borrower['media_type_id'];
			}
			if($borrower['cir_status']){
				$get['where']['lib_circulationfile2.cir_status'] = $borrower['cir_status'];
			}

			if($borrower['user_from']){
				$get['where']['concat_ws("",profiles.lastname," , ",profiles.firstname," ",profiles.middlename) >= '] = $borrower['user_from'];
			}

			if($borrower['user_to']){
				$get['where']['concat_ws("",profiles.lastname," , ",profiles.firstname," ",profiles.middlename) <= '] = $borrower['user_to'];
			}
			
			$get['join'][] = array(
					'table' => 'lib_circulationfile1',
					'on' => 'lib_circulationfile1.id = lib_circulationfile2.circulation_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_books',
					'on' => 'library_books.book_id = lib_circulationfile2.media_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'media_types',
					'on' => 'media_types.id = library_books.media_type_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_book_category',
					'on' => 'library_book_category.lbc_id = library_books.book_category',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'enrollments',
					'on' => 'enrollments.e_id = lib_circulationfile1.borrower_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'profiles',
					'on' => 'profiles.profile_id = enrollments.e_profile_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'levels',
					'on' => 'enrollments.e_grade_level = levels.level_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'block_section',
					'on' => 'enrollments.block = block_section.id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'sections',
					'on' => 'enrollments.e_section_id = sections.id',
					'type' => 'left'
				);
			
			$get['fields'] = array(
					"library_books.book_id",
					"library_books.book_category",
					"library_books.book_name",
					"library_books.accession_number",
					"library_books.call_number",
					"library_books.book_desc",
					"library_books.book_copies",
					"library_books.book_isbn",
					"library_books.book_author",
					"library_books.book_publisher",
					"library_books.book_dop",
					"library_books.book_created",
					"library_books.book_updated",
					"library_books.media_type_id",
					"library_books.book_borrowed",
					"library_books.book_barcode",
					"media_types.media_type",
					"library_book_category.lbc_name as category",
					"lib_circulationfile2.circulation_id",
					"lib_circulationfile2.trndte as trndte_raw",
					"lib_circulationfile2.retdte as retdte_raw",
					"DATE_FORMAT(lib_circulationfile2.trndte,'%m-%d-%Y') as trndte",
					"DATE_FORMAT(lib_circulationfile2.retdte,'%m-%d-%Y') as retdte",
					"lib_circulationfile2.day",
					"lib_circulationfile2.cir_status",
					"lib_circulationfile2.is_late",
					"lib_circulationfile2.day_late",
					"lib_circulationfile2.remarks",
					"lib_circulationfile1.usertype",
					"lib_circulationfile1.borrower_id",
					"lib_circulationfile2.id",
					'concat_ws("",profiles.lastname," , ",profiles.firstname," ",profiles.middlename) as borrowers_fullname',
					'enrollments.e_student_id as idno',
					'levels.level_desc as level_code',
					'block_section.block_name',
					'sections.section_name'
				);

			$rs['transactions'] = $this->get_record(false, $get);
			// vd($rs);
			return $rs;
		}

		if($borrower['usertype'] === "employee"){

			unset($get);
			$get['where']['DATE(lib_circulationfile2.trndte) >= '] = $borrower['from'];
			$get['where']['DATE(lib_circulationfile2.trndte) <= '] = $borrower['to'];
			$get['where']['lib_circulationfile1.usertype'] = 'employee';
			$get['where']['lib_circulationfile2.is_deleted'] = 0;
			$get['order'] = 'borrowers_fullname, lib_circulationfile2.trndte';
			if($borrower['media_type_id']){
				$get['where']['library_books.media_type_id'] = $borrower['media_type_id'];
			}
			if($borrower['cir_status']){
				$get['where']['lib_circulationfile2.cir_status'] = $borrower['cir_status'];
			}

			if($borrower['emp_from']){
				$get['where']['concat_ws("",employees.last_name," , ",employees.first_name," ",employees.middle_name) >= '] = $borrower['emp_from'];
			}

			if($borrower['emp_to']){
				$get['where']['concat_ws("",employees.last_name," , ",employees.first_name," ",employees.middle_name) <= '] = $borrower['emp_to'];
			}
			
			$get['join'][] = array(
					'table' => 'lib_circulationfile1',
					'on' => 'lib_circulationfile1.id = lib_circulationfile2.circulation_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_books',
					'on' => 'library_books.book_id = lib_circulationfile2.media_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'media_types',
					'on' => 'media_types.id = library_books.media_type_id',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'library_book_category',
					'on' => 'library_book_category.lbc_id = library_books.book_category',
					'type' => 'left'
				);
			$get['join'][] = array(
					'table' => 'employees',
					'on' => 'lib_circulationfile1.borrower_id = employees.emp_id',
					'type' => 'left'
				);
			
			$get['fields'] = array(
					"library_books.book_id",
					"library_books.book_category",
					"library_books.book_name",
					"library_books.accession_number",
					"library_books.call_number",
					"library_books.book_desc",
					"library_books.book_copies",
					"library_books.book_isbn",
					"library_books.book_author",
					"library_books.book_publisher",
					"library_books.book_dop",
					"library_books.book_created",
					"library_books.book_updated",
					"library_books.media_type_id",
					"library_books.book_borrowed",
					"library_books.book_barcode",
					"media_types.media_type",
					"library_book_category.lbc_name as category",
					"lib_circulationfile2.circulation_id",
					"lib_circulationfile2.trndte as trndte_raw",
					"lib_circulationfile2.retdte as retdte_raw",
					"DATE_FORMAT(lib_circulationfile2.trndte,'%m-%d-%Y') as trndte",
					"DATE_FORMAT(lib_circulationfile2.retdte,'%m-%d-%Y') as retdte",
					"lib_circulationfile2.day",
					"lib_circulationfile2.cir_status",
					"lib_circulationfile2.is_late",
					"lib_circulationfile2.day_late",
					"lib_circulationfile2.remarks",
					"lib_circulationfile1.usertype",
					"lib_circulationfile1.borrower_id",
					"lib_circulationfile2.id",
					'concat_ws("",employees.last_name," , ",employees.first_name," ",employees.middle_name) as borrowers_fullname',
					'employees.role'
				);

			$rs['transactions'] = $this->get_record(false, $get);
			// vd($rs);
			return $rs;
		}
	}

	/**
	 * circulation report by media
	 * @param array from post
	 */
	public function circulation_report_by_media($media)
	{
		
		$this->load->model(array(
				'M_media_types',
				'M_student',
				'M_library_books',
				'M_library_book_category',
				'M_employees'
			));
		$this->load->model('M_lib_circulationfile1','m_lib_c1');

		unset($get);
		$get['where']['DATE(lib_circulationfile2.trndte) >= '] = $media['from'];
		$get['where']['DATE(lib_circulationfile2.trndte) <= '] = $media['to'];
		$get['where']['lib_circulationfile2.is_deleted'] = 0;
		$get['order'] = 'library_books.book_name, lib_circulationfile2.trndte';
		if($media['media_type_id']){
			$get['where']['library_books.media_type_id'] = $media['media_type_id'];
		}
		if($media['cir_status']){
			$get['where']['lib_circulationfile2.cir_status'] = $media['cir_status'];
		}

		if($media['media_from']){
			$get['where']['library_books.book_name >= '] = $media['media_from'];
		}

		if($media['media_to']){
			$get['where']['library_books.book_name <= '] = $media['media_to'];
		}
		
		$get['join'][] = array(
				'table' => 'lib_circulationfile1',
				'on' => 'lib_circulationfile1.id = lib_circulationfile2.circulation_id',
				'type' => 'left'
			);
		$get['join'][] = array(
				'table' => 'library_books',
				'on' => 'library_books.book_id = lib_circulationfile2.media_id',
				'type' => 'left'
			);
		$get['join'][] = array(
				'table' => 'media_types',
				'on' => 'media_types.id = library_books.media_type_id',
				'type' => 'left'
			);
		$get['join'][] = array(
				'table' => 'library_book_category',
				'on' => 'library_book_category.lbc_id = library_books.book_category',
				'type' => 'left'
			);
		$get['fields'] = array(
				"library_books.book_id",
				"library_books.book_category",
				"library_books.book_name",
				"library_books.accession_number",
				"library_books.call_number",
				"library_books.book_desc",
				"library_books.book_copies",
				"library_books.book_isbn",
				"library_books.book_author",
				"library_books.book_publisher",
				"library_books.book_dop",
				"library_books.book_created",
				"library_books.book_updated",
				"library_books.media_type_id",
				"library_books.book_borrowed",
				"library_books.book_barcode",
				"media_types.media_type",
				"library_book_category.lbc_name as category",
				"lib_circulationfile2.circulation_id",
				"lib_circulationfile2.trndte as trndte_raw",
				"lib_circulationfile2.retdte as retdte_raw",
				"DATE_FORMAT(lib_circulationfile2.trndte,'%m-%d-%Y') as trndte",
				"DATE_FORMAT(lib_circulationfile2.retdte,'%m-%d-%Y') as retdte",
				"lib_circulationfile2.day",
				"lib_circulationfile2.cir_status",
				"lib_circulationfile2.is_late",
				"lib_circulationfile2.day_late",
				"lib_circulationfile2.remarks",
				"lib_circulationfile1.usertype",
				"lib_circulationfile1.borrower_id",
				"lib_circulationfile2.id",
			);

		$rs = $this->get_record(false, $get);
		
		$borrowers = false;
		if($rs){
			foreach ($rs as $k => $v) {
				
				/** GET BORROWERS INFO FROM EACH RECORD DEPENDS ON USERTYPE (STUDENT|EMPLOYEE) **/
				if($v->usertype === "student"){
					$user = $this->M_student->get_student($v->borrower_id);
				}else{
					$user = $this->M_employees->get($v->borrower_id);
				}

				$borrowers[$v->id] = $user;
			}
		}

		$sly['transactions'] = $rs;
		$sly['borrowers'] = $borrowers;
		
		return $sly;
		
	}
}